# 第一题
class T1Spider(scrapy.Spider):
    name = 't1'
    allowed_domains = ['www.imooc.com/course/list']
    start_urls = ['http://www.imooc.com/course/list/']

    def parse(self, response):
        myitem = Project1Item()
        x1 = response.xpath("//p[@class='title ellipsis2']/text()").getall()
        x2 = response.xpath("//p[@class='one']/text()").getall()
        x3 = response.xpath("//p[@class='two clearfix']/span/text()").getall()
        x4 = response.xpath("//div[@class='img']//@style").getall()
        print(x1,x2,x3,x4)
        for (ix1,ix2,ix3,ix4) in zip(x1,x2,x3,x4):
            myitem["x1"] = ix1
            myitem["x2"] = ix2
            myitem["x3"] = ix3
            myitem["x4"] = ix4.split(' ')[-1]
            yield myitem   
class Project1Pipeline:
    def open_spider(self,spider):
        db_name = spider.settings.get('SQLITE_DB_NAME','t1.db')
        self.dbconnect = sqlite3.connect(db_name)
        self.dbcursor = self.dbconnect.cursor()
        

    def close_spider(self,spider):
        self.dbconnect.commit()
        self.dbconnect.close()

    def process_item(self, item, spider):
        self.insert_data(item)
        return item


    def insert_data(self,item):
        values = (
            item['x1'],
            item['x2'],
            item['x3'],
            item['x4'],
        )
        sql = 'INSERT INTO hohoho1 VALUES(?,?,?,?)'
        self.dbcursor.execute(sql, values)
# 第二题
class T1Spider(scrapy.Spider):
    name = 't1'
    allowed_domains = ['www.1ppt.com/moban/']
    start_urls = ['http://www.1ppt.com/moban/']

    def parse(self, response):
        myitem = Project1Item()
        x1 = response.xpath("//li/h2/a/text()").getall()
        x2 = response.xpath("//li/span/text()[2]").getall()
        x3 = response.xpath("//li/a/img//@src").getall()
        print(x1,x2,x3)
        for (ix1,ix2,ix3) in zip(x1,x2,x3):
            myitem["x1"] = ix1
            myitem["x2"] = ix2.split(' ')[-1]
            myitem["x3"] = ix3
            yield myitem  
class Project1Pipeline:
    def open_spider(self,spider):
        db_name = spider.settings.get('SQLITE_DB_NAME','t1.db')
        self.dbconnect = sqlite3.connect(db_name)
        self.dbcursor = self.dbconnect.cursor()
        

    def close_spider(self,spider):
        self.dbconnect.commit()
        self.dbconnect.close()

    def process_item(self, item, spider):
        self.insert_data(item)
        return item


    def insert_data(self,item):
        values = (
            item['x1'],
            item['x2'],
            item['x3'],
        )
        sql = 'INSERT INTO hohoho2 VALUES(?,?,?)'
        self.dbcursor.execute(sql, values)
# 第三题
class T1Spider(scrapy.Spider):
    name = 't1'
    allowed_domains = ['jh.lianjia.com/ershoufang/']
    start_urls = ['https://jh.lianjia.com/ershoufang/']

    def parse(self, response):
        myitem = Project1Item()
        x1 = response.xpath("//div[@class='title']/a/text()").getall()
        x2 = response.xpath("//div[@class='houseInfo']/text()").getall()
        x3 = response.xpath("//div[@class='totalPrice totalPrice2']/span/text()").getall()
        x4 = response.xpath("//div[@class='unitPrice']/span/text()").getall()
        print(x1,x2,x3,x4)
        for (ix1,ix2,ix3,ix4) in zip(x1,x2,x3,x4):
            myitem["x1"] = ix1
            myitem["x2"] = ix2
            myitem["x3"] = ix3
            myitem["x4"] = ix4
            yield myitem 
class Project1Pipeline:
    def open_spider(self,spider):
        db_name = spider.settings.get('SQLITE_DB_NAME','t1.db')
        self.dbconnect = sqlite3.connect(db_name)
        self.dbcursor = self.dbconnect.cursor()
        

    def close_spider(self,spider):
        self.dbconnect.commit()
        self.dbconnect.close()

    def process_item(self, item, spider):
        self.insert_data(item)
        return item


    def insert_data(self,item):
        values = (
            item['x1'],
            item['x2'],
            item['x3'],
            item['x4'],
        )
        sql = 'INSERT INTO hohoho3 VALUES(?,?,?,?)'
        self.dbcursor.execute(sql, values)
# 第四题
    def parse(self, response):
        x8 = []
        x9 = []
        myitem = Project1Item()
        x1 = response.xpath("//li[@class='item']/a/p[2]/text()").getall()
        x2 = response.xpath("//div[@class='comm_box clearfix']/p/span/text()").getall()
        x3 = response.xpath("//div[@class='comm_box clearfix']/p[@class='score']/text()").getall()
        x4 = response.xpath("//div[@class='comm_box clearfix']/p[@class='comm_num']/text()").getall()
        x5 = response.xpath("//p[@class='price']/span[@class='y rmb']/text()").getall()
        x6 = response.xpath("//p[@class='price']/text()").getall()
        x7 = response.xpath("//p[@class='price']/span[@class='qi']/text()").getall()
        for i in range(len(x2)):
            x8.append(x2[i]+" "+x3[i]) 
        for i in range(len(x5)):
            x9.append(x5[i]+x6[i]+x7[i]) 
        print(x8,x9)
        for (ix1,ix4,ix8,ix9) in zip(x1,x4,x8,x9):
            myitem["x1"] = ix1
            myitem["x4"] = ix4
            myitem["x8"] = ix8
            myitem["x9"] = ix9
            yield myitem 
class Project1Pipeline:
    def open_spider(self,spider):
        db_name = spider.settings.get('SQLITE_DB_NAME','t1.db')
        self.dbconnect = sqlite3.connect(db_name)
        self.dbcursor = self.dbconnect.cursor()
        

    def close_spider(self,spider):
        self.dbconnect.commit()
        self.dbconnect.close()

    def process_item(self, item, spider):
        self.insert_data(item)
        return item


    def insert_data(self,item):
        values = (
            item['x1'],
            item['x4'],
            item['x8'],
            item['x9'],
        )
        sql = 'INSERT INTO hohoho4 VALUES(?,?,?,?)'
        self.dbcursor.execute(sql, values)

# 第五题
    def parse(self, response):
        myitem = Project1Item()
        xhtml = response.json()
        x1=[]
        x2=[]
        x3=[]
        for i in range(50):
            x1.append(xhtml['payload']['players'][i]['playerProfile']['displayName'])
            x2.append(xhtml['payload']['players'][i]['teamProfile']['displayAbbr'])
            x3.append(xhtml['payload']['players'][i]['statAverage']['pointsPg'])
        print(x1,x2,x3)
        for (ix1,ix2,ix3) in zip(x1,x2,x3):
            myitem["x1"] = ix1
            myitem["x2"] = ix2
            myitem["x3"] = ix3
            yield myitem   
class Project1Pipeline:
    def open_spider(self,spider):
        db_name = spider.settings.get('SQLITE_DB_NAME','t1.db')
        self.dbconnect = sqlite3.connect(db_name)
        self.dbcursor = self.dbconnect.cursor()
        

    def close_spider(self,spider):
        self.dbconnect.commit()
        self.dbconnect.close()

    def process_item(self, item, spider):
        self.insert_data(item)
        return item


    def insert_data(self,item):
        values = (
            item['x1'],
            item['x2'],
            item['x3'],
        )
        sql = 'INSERT INTO hohoho5 VALUES(?,?,?)'
        self.dbcursor.execute(sql, values)